
Create PROC [dbo].[STICirculatingGoodsWithoutTaxID]
    @WarehouseCoode VARCHAR(5),
    @ReportType TINYINT,
    @ComputerName NVARCHAR(MAX)
AS
BEGIN

    IF EXISTS
    (
        SELECT *
        FROM [tempdb].dbo.sysobjects
        WHERE name = '##STITmpGoods'
              AND xtype = 'U'
    )
    BEGIN
        DELETE FROM [tempdb].dbo.##STITmpGoods
        WHERE ComputerName = @ComputerName;
    END;
    ELSE
    BEGIN
        CREATE TABLE ##STITmpGoods
        (
            GoodsCode VARCHAR(15),
            WarehouseCode VARCHAR(5),
            GoodsName NVARCHAR(MAX) COLLATE Arabic_CI_AS,
            ComputerName NVARCHAR(MAX) COLLATE Arabic_CI_AS,
            WarehouseName NVARCHAR(MAX) COLLATE Arabic_CI_AS
        );
    END;
    IF @WarehouseCoode = '0'
        SET @WarehouseCoode = NULL;
    IF @ReportType = 0
    BEGIN

        INSERT INTO ##STITmpGoods
        (
            GoodsCode,
            WarehouseCode,
            GoodsName,
            ComputerName,
            WarehouseName
        )
        SELECT K.K_Code,
               WI.A_Code,
               K.Name1,
               @ComputerName,
               WI.Name
        FROM dbo.STITransaction Tr WITH (NOLOCK)
            INNER JOIN Havaleh D WITH (NOLOCK)
                INNER JOIN headH H
                    ON H.Id_Havaleh = D.Id_Havaleh
                INNER JOIN dbo.KalaId K
                    ON K.K_Code = D.K_Code
                ON (Tr.InvoiceId = H.Id_Havaleh)
            INNER JOIN dbo.AnbarId WI
                ON CASE
                       WHEN H.Kind_Flag IN ( 7, 5, 9 )
                            AND WI.A_Code = D.Sender THEN
                           1
                       WHEN H.Kind_Flag IN ( 8, 6, 0 )
                            AND WI.A_Code = D.Reciver THEN
                           1
                       ELSE
                           0
                   END = 1
        WHERE H.Kind_Flag IN ( 7, 8, 5, 6, 9, 0 )
              AND Tr.Status <> 1
        GROUP BY K.K_Code,
                 K.Name1,
                 WI.A_Code,
                 WI.Name
        HAVING K.K_Code NOT IN
               (
                   SELECT GM.GoodsCode FROM dbo.STIGoodsMapping GM WITH (NOLOCK)
               );



    END;
    IF @ReportType = 1
    BEGIN
        INSERT INTO ##STITmpGoods
        (
            GoodsCode,
            WarehouseCode,
            GoodsName,
            ComputerName,
            WarehouseName
        )
        SELECT K.K_Code,
               NULL,
               K.Name1,
               @ComputerName,
               NULL
        FROM dbo.KalaId K WITH (NOLOCK)
        GROUP BY K.K_Code,
                 K.Name1
        HAVING K.K_Code NOT IN
               (
                   SELECT GM.GoodsCode FROM dbo.STIGoodsMapping GM WITH (NOLOCK)
               );

    END;
    IF @ReportType = 2
    BEGIN

        INSERT INTO ##STITmpGoods
        (
            GoodsCode,
            WarehouseCode,
            GoodsName,
            ComputerName,
            WarehouseName
        )
        SELECT K.K_Code,
               W.A_Code,
               K.Name1,
               @ComputerName,
               WI.Name
        FROM Havaleh D WITH (NOLOCK)
            INNER JOIN headH H
                ON H.Id_Havaleh = D.Id_Havaleh
            INNER JOIN dbo.KalaId K
                ON K.K_Code = D.K_Code
            INNER JOIN Anbar W
                ON CASE
                       WHEN H.Kind_Flag IN ( 7, 5, 9 )
                            AND W.A_Code = D.Sender THEN
                           1
                       WHEN H.Kind_Flag IN ( 8, 6, 0 )
                            AND W.A_Code = D.Reciver THEN
                           1
                       ELSE
                           0
                   END = 1
            INNER JOIN dbo.AnbarId WI
                ON WI.A_Code = W.A_Code
        WHERE H.Kind_Flag IN ( 7, 8, 5, 6, 9, 0 )
        GROUP BY K.K_Code,
                 K.Name1,
                 W.A_Code,
                 WI.Name
        HAVING K.K_Code NOT IN
               (
                   SELECT GM.GoodsCode FROM dbo.STIGoodsMapping GM WITH (NOLOCK)
               );


    END;


    IF @WarehouseCoode IS NULL
    BEGIN
        SELECT GoodsCode,
               ISNULL(WarehouseCode, '') AS WarehouseCode,
               CAST(GoodsName AS NVARCHAR(250)) GoodsName,
               ComputerName,
               CAST(WarehouseName AS NVARCHAR(40)) WarehouseName
        FROM [tempdb].dbo.##STITmpGoods
        WHERE ComputerName = @ComputerName;
    END;
    ELSE
    BEGIN
        SELECT GoodsCode,
               ISNULL(WarehouseCode, '') AS WarehouseCode,
               CAST(GoodsName AS NVARCHAR(250)) GoodsName,
               ComputerName,
               CAST(WarehouseName AS NVARCHAR(40)) WarehouseName
        FROM [tempdb].dbo.##STITmpGoods
        WHERE ComputerName = @ComputerName
              AND ISNULL(WarehouseCode, '') = @WarehouseCoode;
    END;
END;



